param(
	[Parameter(Mandatory=$false)]
	[ValidateScript({test-path $_})]
	[object[]]$source_csv_file			# the source csv files to be combined into one csv file
)
$st=get-date
#--------------------------------- Part Of Excel Data Management------------------------------------------
#<-------------------------------------------------- get source csv files
$this_path = Split-Path -Parent ($MyInvocation.MyCommand.Definition) # get the directory of this running script
If( !$PSBoundParameters.ContainsKey('source_csv_file') ){ # if no csv files have been provided
    $source_csv_file=@()
	Foreach( $d in (dir $this_path|?{$_.PsIsContainer})){ # it will collect all the csv files from the subfolder's directory
	    $source_csv_file+=dir "$($d.fullname)\*.csv"
	}
}
$source_csv_file=$source_csv_file|select -Unique
If( $source_csv_file.count -eq 0 ){Popup-Message "No source csvFile has been provided!";return}
#<-------------------------------------------------- enable macro running
$macro_key="HKCU\Software\Microsoft\Office\$($excel.Version)\excel\Security"
reg delete $macro_key /v AccessVBOM /f 1>$null 2>&1
reg delete $macro_key /v VBAWarnings /f 1>$null 2>&1
#<--------------------------------------------------open excel and start to combine csv files
write-host "-------------------Starting to combine below csv files-------------------"
Try{ # the script will quit if no excel has been installed in the running computer
	$excel = new-object -comobject excel.application ; # $excel.Visible=$true
}Catch{
    $_ ; return
}Finally{}
$excel.DisplayAlerts=$false # disable warning popup when makeing changes
$workbook = $excel.workbooks.open("$this_path\report_template.xlsm")
Foreach( $sr in $source_csv_file){ # copy each csv data to report_template.xlsm
	$sr=(get-item $sr).FullName
	If( test-path $sr -ea 0){
		write-host "Combinding $sr.."
		$excel.Run("CopyCsvFile",$sr) # run the macro in report_template.xlsm
	}else{
	    Write-Error "$sr cannot be found!"
	}
}
#<--------------------------------------- delete old report csv file forcely and save a new one
$report_file_name="$this_path\comparision_report.xlsx"
del $report_file_name -force -ea 0
# $workbook.saveas($report_file_name,[Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV)
$workbook.saveas($report_file_name,[Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault)
$excel.DisplayAlerts=$true # enable warning popup when makeing changes
#<-------------------------------------------------- completely quit and remove excel com-object
$workbook.close() ; $excel.quit() ; [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) 1>$null ; Remove-Variable excel
$run_secs=[math]::Round(((get-date)-$st).totalseconds)
Write-Host -ForegroundColor Yellow "The report file has been stored in $report_file_name in $run_secs s."
